<?php
session_start();
include "dbconnect.php";
if($_SESSION["Username"]!="admin")
{
	echo "<h3>Unauthorized Access</h3>";
echo "<script>setTimeout(\"location.href = 'index.php';\",2000);</script>";
return;
}
$pushdata=$_POST["pushdata"];
if($pushdata==1)
{
	$courseid=$_POST["courseid"];
	$instructor=$_POST["instructor"];
	$notes=$_POST["notes"];
	$totalslots=$_POST["totalslots"];
	$filledslots=$_POST["filledslots"];
	$waitingslots=$_POST["waitingslots"];
	$section=$_POST["section"];
	$location=$_POST["location"];
	$day=$_POST["day"];
	$timing=$_POST["timings"];
	$term=$_POST["sem"];
	$year=$_POST["ayear"];
	$con=mysql_connect($dbHost,$dbUserAndName,$dbPass);
	mysql_select_db($_DB_NAME,$con);
	$semid=mysql_query("Select semid from semester where term='".$term."' and year='".$year."'");
	$semid=mysql_fetch_array($semid);
	$instructors=mysql_fetch_array(mysql_query("Select PID from professor where P_ShortName='".$instructor."'"));
	if(empty($instructors[0]))
	{
		echo "Invalid Professor Short Name";
		return;
	}
	$result=mysql_query("Select * from openings where courseid='".$courseid."'");
	$result=mysql_fetch_array($result);
	$resultb=mysql_query("Select * from openings where courseid='".$courseid."' and instructor='".$instructors[0]."'");
	$resultb=mysql_fetch_array($resultb);
	if(empty($result[0])==true)
	{
		mysql_query("Insert into courses (courseid, description) values ('".$courseid."',NULL)");
		mysql_query("Insert into openings (courseid, sec, instructor, semid, specialreq, location, Total, Filled, Waiting, isclosed, isdeleted, Timing, day) values ('".$courseid."','".$section."','".$instructors[0]."','".$semid[0]."','".$notes."','".$location."','".$totalslots."','".$filledslots."','".$waitingslots."',0,0,'".$timing."','".$day."')");
	}
	else if(empty($resultb[0])==true)
	{
		mysql_query("Insert into openings (courseid, sec, instructor, semid, specialreq, location, Total, Filled, Waiting, isclosed, isdeleted, Timing, day) values ('".$courseid."','".$section."','".$instructors[0]."','".$semid[0]."','".$notes."','".$location."','".$totalslots."','".$filledslots."','".$waitingslots."',0,0,'".$timing."','".$day."')");
	}
	else
	{	
		$section1=explode(",",$result[10]);
		$i=1;
		while($i!=count($section1))
		{
			if($section==$section1[$i])
			{
				echo "Data already exist";
				echo "<script>setTimeout(\"location.href = 'post.php';\",2000);</script>";
				return;
			}
			$i++;
		}	
		$section=$result[10].",".$section;
		//$instructor=$result[2].",".$instructor;
		if(empty($result[3])==true)
		{
			$notes=$result[3];
		}
		else
		{
			$notes=$result[3].",".$notes;
		}
		$location=$result[11].",".$location;
		$totalslots=$totalslots+$result[7];
		$filledslots=$filledslots+$result[8];
		$waitingslots=$waitingslots+$result[9];
		$timing=$result[9].";".$day.",".$timing;
		$day=$result[8].";".$day;
		mysql_query("update openings set specialreq='".$notes."', total='".$totalslots."', filled='".$filledslots."', waiting='".$waitingslots."', timing='".$timing."', day='".$day."', sec='".$section."', location='".$location."' where courseid='".$courseid."' and instructor='".$instructor."'");
	}
	echo "Row updated/inserted";
	echo "<script>setTimeout(\"location.href = 'post.php';\",2000);</script>";
	return;	
}

//File
$filename=$_FILES["postcsv"];
$term=$_POST["sem"];
$year=$_POST["ayear"];
if ($filename["error"] !== UPLOAD_ERR_OK) 
{
	echo "Upload Error.";
    echo "<script>setTimeout(\"location.href = 'post.php';\",2000);</script>";
}
$success=move_uploaded_file($_FILES["postcsv"]["tmp_name"], $_FILES["postcsv"]["name"]);
if(!$success)
{
	echo "Upload Error.";
	echo "<script>setTimeout(\"location.href = 'post.php';\",2000);</script>";
}
else
{
	$con=mysql_connect($dbHost,$dbUserAndName,$dbPass);
	mysql_select_db($_DB_NAME,$con);
	$semid=mysql_query("Select semid from semester where term='".$term."' and year='".$year."'");
	$semid=mysql_fetch_array($semid);
	mysql_query("delete from openings where semid='".$semid[0]."'");
	$file = fopen($_FILES["postcsv"]["name"],"r");
	fgetcsv($file);
	fgetcsv($file);
	fgetcsv($file);
	$result=fgetcsv($file);
	while(! feof($file))
	{
		
		if(empty($result[0])==true || !preg_match("/\b[A-Z]\d\d\d\b/",$result[0]))
		{
			continue;	
		}
		if(mysql_num_rows(mysql_query("select * from courses where courseid like '%".$result[0]."%'"))==0)
		{
			mysql_query("Insert into courses (courseid, description) values ('".$result[0]."',NULL)");
		}
		$Total=0;
		$Filled=0;
		$Waiting=0;
		$day="";
		$Timing="";
		$instructor="";
		$sec="";
		$location="";
		while(!feof($file))
		{
			$result1=fgetcsv($file);
			if(empty($result1[0])==true || !preg_match("/[A-Z][0-9][0-9][0-9]/",$result1[0]))
			{
				continue;	
			}
			if(trim($result1[0])==trim($result[0]) && trim($result1[6])==trim($result[6]))
			{
				$result1[8]=str_replace(' ', '', $result1[8]);			
				$Total=$Total+substr($result1[8],0,strpos($result1[8],"-"));
			    $Filled=$Filled+substr($result1[8],strpos($result1[8],"-")+1,strpos($result1[8],"=")-(strpos($result1[8],"-")+1));
			    $Waiting=$Waiting+substr($result1[8],strpos($result1[8],"(")+1,strpos($result1[8],")")-(strpos($result1[8],"(")+1));				
				if(empty($Timing)==true)
				{
					$day=$result1[4];
					$Timing=$result1[4].",".$result1[3];
				}
				else
				{
					$day=$day.";".$result1[4];
					$Timing=$Timing.";".$result1[4].",".$result1[3];
				}
				if(empty($sec)==true)
				{
					$sec=$result1[1];
				}
				else
				{
					$sec=$sec.";".$result1[1];
				}
				if(empty($location)==true)
				{
					$location=$result1[5];
				}
				else
				{
					$location=$location.";".$result1[5];
				}	
			}
			
			else
			{
				
				$Total=$Total+substr($result[8],0,strpos($result[8],"-"));
			    $Filled=$Filled+substr($result[8],strpos($result[8],"-")+1,strpos($result[8],"=")-(strpos($result[8],"-")+1));
			    $Waiting=$Waiting+substr($result[8],strpos($result[8],"(")+1,strpos($result[8],")")-(strpos($result[8],"(")+1));
				$instructor=mysql_fetch_array(mysql_query("select * from professor where P_ShortName='".trim($result[6])."'"));	
				if(empty($Timing)==true)
				{
					$day=$result[4];
					$Timing=$result[4].",".$result[3];
				}
				else
				{
					$day=$result[4].";".$day;
					$Timing=$result[4].",".$result[3].";".$Timing;	
				}
				if(empty($sec)==true)
				{
					$sec=$result[1];
				}
				else
				{
					$sec=$result[1].";".$sec;
				}
				if(empty($location)==true)
				{
					$location=$result[5];
				}
				else
				{
					$location=$result[5].";".$location;
				}
				if(!empty($instructor[0]))
				{
					$temp=mysql_fetch_array(mysql_query("select * from openings where courseid='".$result[0]."' and instructor='".$instructor[0]."' and semid='".$semid[0]."'"));
					if(empty($temp[0]))
					{
						mysql_query("Insert into openings (courseid, sec, instructor, semid, specialreq, location, Total, Filled, Waiting, isclosed,isdeleted, day, Timing) values ('".$result[0]."','".$sec."','".$instructor[0]."','".$semid[0]."','".$result[7]."','".$location."','".$Total."','".$Filled."','".$Waiting."',0,0,'".$day."','".$Timing."')");
					}
					else
					{
						$sec=$temp[10].";".$sec;
						$result[7]=$temp[3].";".$result[7];
						$location=$temp[11].";".$location;
						$Total=$Total+$temp[5];
						$Filled=$Filled+$temp[6];
						$Waiting=$Waiting+$temp[7];
						$day=$temp[8].";".$day;
						$Timing=$temp[9].";".$Timing;
						mysql_query("Update openings set sec='".$sec."', specialreq='".$result[7]."', location='".$location."', Total='".$Total."', Filled='".$Filled."', Waiting='".$Waiting."', Day='".$day."', Timing='".$Timing."' where courseid='".trim($result[0])."' and instructor='".trim($instructor[0])."'");
					}
					
				}
				$result=$result1;
				break;
				
			}
		}
	}


			if(feof($file))
			{
				$Total=$Total+substr($result[8],0,strpos($result[8],"-"));
				$Filled=$Filled+substr($result[8],strpos($result[8],"-")+1,strpos($result[8],"=")-(strpos($result[8],"-")+1));
			    $Waiting=$Waiting+substr($result[8],strpos($result[8],"(")+1,strpos($result[8],")")-(strpos($result[8],"(")+1));
				if(empty($Timing)==true)
				{
					$day=$result[4];
					$Timing=$result[4].",".$result[3];
				}
				else
				{
					$day=$result[4].";".$day;
					$Timing=$result[4].",".$result[3].";".$Timing;
				}
				if(empty($sec)==true)
				{
					$sec=$result[1];	
				}
				else
				{
					$sec=$result[1].";".$sec;
				}
				if(empty($location)==true)
				{
					$location=$result[5];
				}
				else
				{
					$location=$result[5].";".$location;
				}
				$instructor=mysql_fetch_array(mysql_query("select * from professor where P_ShortName='".trim($result[6])."'"));
				if(!empty($instructor[0]))
				{
					$temp=mysql_fetch_array(mysql_query("select * from openings where courseid='".$result[0]."' and instructor='".$instructor[0]."' and semid='".$semid[0]."'"));
					if(empty($temp[0]))
					{
						mysql_query("Insert into openings (courseid, sec, instructor, semid, specialreq, location, Total, Filled, Waiting, isclosed,isdeleted, day, Timing) values ('".$result[0]."','".$sec."','".$instructor[0]."','".$semid[0]."','".$result[7]."','".$location."','".$Total."','".$Filled."','".$Waiting."',0,0,'".$day."','".$Timing."')");
					}
					else
					{
						$sec=$temp[10].";".$sec;
						$result[7]=$temp[3].";".$result[7];
						$location=$temp[11].";".$location;
						$Total=$Total+$temp[5];
						$Filled=$Filled+$temp[6];
						$Waiting=$Waiting+$temp[7];
						$day=$temp[8].";".$day;
						$Timing=$temp[9].";".$Timing;
						mysql_query("Update openings set sec='".$sec."', specialreq='".$result[7]."', location='".$location."', Total='".$Total."', Filled='".$Filled."', Waiting='".$Waiting."', Day='".$day."', Timing='".$Timing."' where courseid='".trim($result[0])."' and instructor='".trim($instructor[0])."'");
					}
					
				}
		    }
echo "<h3>Data Pushed Successfully</h3>";
echo "<script>setTimeout(\"location.href = 'post.php';\",2000);</script>";
}
?>